const { query, transaction, formatRows, buildWhereClause, buildPagination } = require('../config/database');

class Appointment {
  // 创建预约
  static async create(appointmentData) {
    const {
      title,
      game_name,
      category_id,
      description,
      creator_id,
      max_players,
      scheduled_time,
      requirements,
      contact_info,
      is_public = true
    } = appointmentData;

    return await transaction(async (connection) => {
      // 插入预约记录
      const appointmentSql = `
        INSERT INTO appointments (
          title, game_name, category_id, description, creator_id, 
          max_players, scheduled_time, requirements, contact_info, is_public
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const [result] = await connection.execute(appointmentSql, [
        title,
        game_name,
        category_id || null,
        description,
        creator_id,
        max_players,
        scheduled_time,
        requirements || null,
        contact_info || null,
        is_public
      ]);
      
      const appointmentId = result.insertId;
      
      // 将创建者添加为参与者
      const participantSql = `
        INSERT INTO appointment_participants (appointment_id, user_id, role)
        VALUES (?, ?, 'creator')
      `;
      
      await connection.execute(participantSql, [appointmentId, creator_id]);

      return appointmentId;
    });
  }
  
  // 根据ID查找预约
  static async findById(id) {
    const sql = `
      SELECT 
        a.*,
        u.username as creator_username,
        u.nickname as creator_nickname,
        gc.name as category_name,
        gc.icon as category_icon
      FROM appointments a
      LEFT JOIN users u ON a.creator_id = u.id
      LEFT JOIN game_categories gc ON a.category_id = gc.id
      WHERE a.id = ?
    `;
    
    const rows = await query(sql, [id]);
    if (rows.length === 0) return null;
    
    const appointment = formatRows(rows)[0];
    
    // 获取参与者列表
    appointment.participants = await this.getParticipants(id);
    
    // 获取申请列表
    appointment.applications = await this.getApplications(id);
    
    return appointment;
  }
  
  // 获取预约列表
  static async getList(conditions = {}, page = 1, limit = 10, orderBy = 'created_at DESC') {
    const { clause, params } = buildWhereClause(conditions);
    const { clause: paginationClause } = buildPagination(page, limit);
    
    // 获取总数
    const countSql = `
      SELECT COUNT(*) as total 
      FROM appointments a
      LEFT JOIN users u ON a.creator_id = u.id
      LEFT JOIN game_categories gc ON a.category_id = gc.id
      ${clause}
    `;
    
    const countResult = await query(countSql, params);
    const total = countResult[0].total;
    
    // 获取列表数据
    const listSql = `
      SELECT 
        a.*,
        u.username as creator_username,
        u.nickname as creator_nickname,
        gc.name as category_name,
        gc.icon as category_icon
      FROM appointments a
      LEFT JOIN users u ON a.creator_id = u.id
      LEFT JOIN game_categories gc ON a.category_id = gc.id
      ${clause}
      ORDER BY ${orderBy}
      ${paginationClause}
    `;
    
    const rows = await query(listSql, params);
    const appointments = formatRows(rows);
    
    // 为每个预约获取参与者数量
    for (const appointment of appointments) {
      const participantCount = await this.getParticipantCount(appointment.id);
      appointment.current_players = participantCount;
      
      // 获取简化的参与者列表（只包含用户名）
      const participants = await this.getParticipants(appointment.id);
      appointment.participants = participants.map(p => p.username);
    }
    
    return {
      data: appointments,
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
      }
    };
  }
  
  // 获取参与者列表
  static async getParticipants(appointmentId) {
    const sql = `
      SELECT 
        ap.id,
        ap.role,
        ap.joined_at,
        ap.notes,
        u.id as user_id,
        u.username,
        u.nickname,
        u.avatar_url
      FROM appointment_participants ap
      JOIN users u ON ap.user_id = u.id
      WHERE ap.appointment_id = ?
      ORDER BY ap.joined_at ASC
    `;
    
    const rows = await query(sql, [appointmentId]);
    return formatRows(rows);
  }
  
  // 获取参与者数量
  static async getParticipantCount(appointmentId) {
    const sql = `
      SELECT COUNT(*) as count 
      FROM appointment_participants 
      WHERE appointment_id = ?
    `;
    
    const rows = await query(sql, [appointmentId]);
    return rows[0].count;
  }
  
  // 获取申请列表
  static async getApplications(appointmentId) {
    const sql = `
      SELECT 
        app.*,
        u.username as applicant_username,
        u.nickname as applicant_nickname,
        u.avatar_url as applicant_avatar,
        processor.username as processor_username
      FROM applications app
      JOIN users u ON app.applicant_id = u.id
      LEFT JOIN users processor ON app.processed_by = processor.id
      WHERE app.appointment_id = ?
      ORDER BY app.applied_at DESC
    `;
    
    const rows = await query(sql, [appointmentId]);
    return formatRows(rows);
  }
  
  // 更新预约
  static async update(appointmentId, updateData, userId) {
    // 验证用户是否为创建者
    const appointment = await this.findById(appointmentId);
    if (!appointment) {
      throw new Error('预约不存在');
    }
    
    if (appointment.creator_id !== userId) {
      throw new Error('只有创建者可以修改预约');
    }
    
    const allowedFields = [
      'title', 'game_name', 'category_id', 'description', 
      'max_players', 'scheduled_time', 'requirements', 
      'contact_info', 'is_public', 'status'
    ];
    
    const updates = [];
    const params = [];
    
    for (const [key, value] of Object.entries(updateData)) {
      if (allowedFields.includes(key) && value !== undefined) {
        updates.push(`${key} = ?`);
        params.push(value);
      }
    }
    
    if (updates.length === 0) {
      throw new Error('没有有效的更新字段');
    }
    
    params.push(appointmentId);
    
    const sql = `
      UPDATE appointments 
      SET ${updates.join(', ')}, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
    
    await query(sql, params);
    return await this.findById(appointmentId);
  }
  
  // 删除预约
  static async delete(appointmentId, userId) {
    const appointment = await this.findById(appointmentId);
    if (!appointment) {
      throw new Error('预约不存在');
    }
    
    if (appointment.creator_id !== userId) {
      throw new Error('只有创建者可以删除预约');
    }
    
    const sql = `DELETE FROM appointments WHERE id = ?`;
    await query(sql, [appointmentId]);
    return true;
  }
  
  // 检查用户是否已参与预约
  static async isUserParticipant(appointmentId, userId) {
    const sql = `
      SELECT COUNT(*) as count 
      FROM appointment_participants 
      WHERE appointment_id = ? AND user_id = ?
    `;
    
    const rows = await query(sql, [appointmentId, userId]);
    return rows[0].count > 0;
  }
  
  // 添加参与者
  static async addParticipant(appointmentId, userId, role = 'participant', notes = '') {
    // 检查预约是否存在且未满员
    const appointment = await this.findById(appointmentId);
    if (!appointment) {
      throw new Error('预约不存在');
    }
    
    if (appointment.current_players >= appointment.max_players) {
      throw new Error('预约已满员');
    }
    
    // 检查用户是否已参与
    const isParticipant = await this.isUserParticipant(appointmentId, userId);
    if (isParticipant) {
      throw new Error('用户已参与此预约');
    }
    
    return await transaction(async (connection) => {
      // 添加参与者
      const participantSql = `
        INSERT INTO appointment_participants (appointment_id, user_id, role, notes)
        VALUES (?, ?, ?, ?)
      `;
      
      await connection.execute(participantSql, [appointmentId, userId, role, notes]);
      
      // 更新当前人数
      const updateSql = `
        UPDATE appointments 
        SET current_players = current_players + 1,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `;
      
      await connection.execute(updateSql, [appointmentId]);
      
      return true;
    });
  }
  
  // 移除参与者
  static async removeParticipant(appointmentId, userId) {
    const isParticipant = await this.isUserParticipant(appointmentId, userId);
    if (!isParticipant) {
      throw new Error('用户未参与此预约');
    }
    
    return await transaction(async (connection) => {
      // 移除参与者
      const participantSql = `
        DELETE FROM appointment_participants 
        WHERE appointment_id = ? AND user_id = ?
      `;
      
      await connection.execute(participantSql, [appointmentId, userId]);
      
      // 更新当前人数
      const updateSql = `
        UPDATE appointments 
        SET current_players = current_players - 1,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `;
      
      await connection.execute(updateSql, [appointmentId]);
      
      return true;
    });
  }
}

module.exports = Appointment;
